```html
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>MySQL 数据库查询学习指南</title>
    <link href="https://cdn.staticfile.org/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
    <link href="https://cdn.staticfile.org/tailwindcss/2.2.19/tailwind.min.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@400;500;600;700&family=Noto+Sans+SC:wght@300;400;500;700&display=swap" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/mermaid@latest/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: 'Noto Sans SC', 'Noto Serif SC', Tahoma, Arial, Roboto, "Droid Sans", "Helvetica Neue", "Droid Sans Fallback", "Heiti SC", "Hiragino Sans GB", Simsun, sans-serif;
            color: #333;
            line-height: 1.6;
            background-color: #f8f9fa;
        }
        .hero {
            background: linear-gradient(135deg, #6e8efb, #a777e3);
            color: white;
        }
        .code-block {
            background-color: #2d3748;
            color: #f8f9fa;
            border-radius: 0.5rem;
            overflow-x: auto;
        }
        .table-container {
            overflow-x: auto;
        }
        table {
            min-width: 100%;
            border-collapse: collapse;
        }
        th, td {
            padding: 0.75rem;
            text-align: left;
            border-bottom: 1px solid #e2e8f0;
        }
        th {
            background-color: #edf2f7;
            font-weight: 600;
        }
        tr:hover {
            background-color: #f8fafc;
        }
        .card {
            transition: transform 0.3s ease, box-shadow 0.3s ease;
        }
        .card:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 25px rgba(0, 0, 0, 0.1);
        }
        .section-icon {
            color: #4f46e5;
            margin-right: 0.5rem;
        }
        .highlight {
            background-color: #e6fffa;
            padding: 0.2rem 0.4rem;
            border-radius: 0.25rem;
            font-weight: 500;
        }
    </style>
</head>
<body class="antialiased">
    <!-- Hero Section -->
    <section class="hero py-20 px-4 md:px-0">
        <div class="container mx-auto max-w-5xl">
            <div class="flex flex-col items-center text-center">
                <h1 class="text-4xl md:text-5xl font-bold mb-6">MySQL 数据库查询学习指南</h1>
                <p class="text-xl opacity-90 max-w-2xl mb-8">掌握从基础到高级的MySQL查询技巧，提升数据库操作能力</p>
                <div class="flex space-x-4">
                    <span class="px-4 py-2 bg-white bg-opacity-20 rounded-full">关系型数据库</span>
                    <span class="px-4 py-2 bg-white bg-opacity-20 rounded-full">SQL查询</span>
                    <span class="px-4 py-2 bg-white bg-opacity-20 rounded-full">数据操作</span>
                </div>
            </div>
        </div>
    </section>

    <!-- Main Content -->
    <div class="container mx-auto max-w-5xl px-4 md:px-0 py-12">
        <!-- Table Structure Section -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-table section-icon"></i>
                <span>1. 初始化三张表</span>
            </h2>
            <p class="mb-6 text-lg">员工表，部门表，工资等级表以这三张表为例来学习MySQL。</p>
            
            <!-- Dept Table -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">1.1 部门表dept</h3>
                <p class="mb-4">部门表用来存放公司所有部门信息，表结构如下：</p>
                <div class="table-container mb-6">
                    <table class="w-full">
                        <thead>
                            <tr>
                                <th>序号</th>
                                <th>字段名称</th>
                                <th>字段类型</th>
                                <th>描述</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>1</td>
                                <td>DEPTNO</td>
                                <td>INT</td>
                                <td>部门编号</td>
                            </tr>
                            <tr>
                                <td>2</td>
                                <td>DNAME</td>
                                <td>VARCHAR</td>
                                <td>部门名称</td>
                            </tr>
                            <tr>
                                <td>3</td>
                                <td>LOC</td>
                                <td>VARCHAR</td>
                                <td>部门位置</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
                <p class="mb-4">部门表数据如下：</p>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406925954-12a4d554-f92e-40bd-a05e-331422133715.png" alt="部门表数据" class="w-full rounded-lg shadow">
            </div>
            
            <!-- EMP Table -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">1.2 员工表 EMP</h3>
                <p class="mb-4">员工表用来存放公司所有员工，表结构如下：</p>
                <div class="table-container mb-6">
                    <table class="w-full">
                        <thead>
                            <tr>
                                <th>序号</th>
                                <th>字段名称</th>
                                <th>字段类型</th>
                                <th>描述</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>1</td>
                                <td>EMPNO</td>
                                <td>INT</td>
                                <td>员工编号</td>
                            </tr>
                            <tr>
                                <td>2</td>
                                <td>ENAME</td>
                                <td>VARCHAR</td>
                                <td>员工姓名</td>
                            </tr>
                            <tr>
                                <td>3</td>
                                <td>JOB</td>
                                <td>VARCHAR</td>
                                <td>职位</td>
                            </tr>
                            <tr>
                                <td>4</td>
                                <td>MGR</td>
                                <td>INT</td>
                                <td>员工领导编号</td>
                            </tr>
                            <tr>
                                <td>5</td>
                                <td>HIREDATE</td>
                                <td>DATE</td>
                                <td>入职时间</td>
                            </tr>
                            <tr>
                                <td>6</td>
                                <td>SAL</td>
                                <td>INT</td>
                                <td>基本工资</td>
                            </tr>
                            <tr>
                                <td>7</td>
                                <td>COMM</td>
                                <td>INT</td>
                                <td>奖金</td>
                            </tr>
                            <tr>
                                <td>8</td>
                                <td>DEPTNO</td>
                                <td>INT</td>
                                <td>员工所属部门编号</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
                <p class="mb-4">员工表数据如下：</p>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406926475-ed42dc5e-0449-4d45-8d0d-388368575ddd.png" alt="员工表数据" class="w-full rounded-lg shadow">
            </div>
            
            <!-- Salgrade Table -->
            <div class="card bg-white rounded-lg shadow-md p-6">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">1.3 工资等级表salgrade</h3>
                <p class="mb-4">工资等级表保存了每个等级的一个最高和最低工资，表结构如下：</p>
                <div class="table-container mb-6">
                    <table class="w-full">
                        <thead>
                            <tr>
                                <th>序号</th>
                                <th>字段名称</th>
                                <th>字段类型</th>
                                <th>描述</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>1</td>
                                <td>GRADE</td>
                                <td>INT</td>
                                <td>工资等级编号</td>
                            </tr>
                            <tr>
                                <td>2</td>
                                <td>LOSAL</td>
                                <td>INT</td>
                                <td>该等级的最低工资</td>
                            </tr>
                            <tr>
                                <td>3</td>
                                <td>HISAL</td>
                                <td>INT</td>
                                <td>该等级的最高工资</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
                <p class="mb-4">工资等级表的数据如下：</p>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406926941-c59c4ca9-52eb-4a00-9bbb-3ef70630bc3e.png" alt="工资等级表数据" class="w-full rounded-lg shadow">
            </div>
        </section>

        <!-- Simple Queries Section -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-search section-icon"></i>
                <span>2. 简单查询</span>
            </h2>
            
            <!-- 2.1 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">2.1 查询emp中所有数据</h3>
                <p class="mb-4">MySQL也是关系型数据库。*代表现实所有的列。</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406927550-a9070099-c3a9-4cf9-b2b3-bcc8e721b750.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 2.2 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">2.2 投影</h3>
                <p class="mb-4">投影就是显示表中的的部分字段，sql中写了三个字段，最后的结果应该展示三列。</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT empno, ename, job FROM emp;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406927989-f1eb513f-ee6f-4bf6-93b5-2d1736568819.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 2.3 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">2.3 别名</h3>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT empno AS "员工编号", ename AS "员工姓名", job AS "职位" FROM emp;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406928479-af1313dc-26bc-4cde-bd46-645a98182809.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 2.4 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">2.4 去掉重复内容</h3>
                <p class="mb-4">查询员工表中所有的员工的职位</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT job FROM emp;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406928952-38e8f912-84fa-4350-b047-9453fbe93c5a.png" alt="查询结果" class="w-full rounded-lg shadow mb-4">
                <p class="mb-4">最后的结果发现有重复的数据，如果想去除重复可以用DISTINCT关键字</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT DISTINCT job FROM emp;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406929178-4b2a07ec-4c28-43c5-8ed5-43180512002c.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mt-4">DISTINCT的去重原理是以一行数据来判断的，如果两行数据中每列的数据都一样才叫重复，否则就是不重复</p>
            </div>
            
            <!-- 2.5 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">2.5 连接显示</h3>
                <p class="mb-4">连接显示就是将两列数据合并成一列来显示，Oracle中字段和字段的链接用"||"和MySQL中的concat函数一样</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT CONCAT(ename, '的职位是', job) AS "员工信息" FROM emp;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406929551-4a4c264a-93e0-4de6-b5bb-909a75992baa.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 2.6 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">2.6 限定查询</h3>
                <p class="mb-4">限定查询就是按照条件查询，只选出对自己有用的数据，可以通过WHERE指定筛选条件，WHERE后面可以跟多个筛选条件。</p>
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">2.6.1 统计基本工资高于2000的员工</h4>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE sal > 2000;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406930047-545c090b-e4d5-4d28-8ef5-44704d7c4a45.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">2.6.2 查询工资范围在1500~3000(包含1500和3000)的所有员工信息</h4>
                <p class="mb-2">1. 通过AND关键字</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE sal >= 1500 AND sal <= 3000;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406930532-a575317e-a256-4f70-89c3-7d52a6d0ba58.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">2.6.3 查询10部门或20部门的所有员工信息</h4>
                <p class="mb-2">1. 第一种方式通过OR关键字</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE deptno = 10 OR deptno = 20;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406930787-03956ab3-5051-41c7-bb56-28c4929f2afb.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mb-2 mt-4">2. 第二种方式通过IN关键字</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE deptno IN (10, 20);</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406931126-8e8679e7-5fc6-4f38-a51d-e479cfe90c1c.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">2.6.4 判断是否为空</h4>
                <p class="mb-2">判断字段是否为空可以由以下语法查询</p>
                <p class="mb-2">1. IS NOT NULL 代表字段值不为NULL</p>
                <p class="mb-4">2. IS NULL 代表字段值为NULL</p>
                <p class="mb-2">查询出所有有奖金的员工</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE comm IS NOT NULL;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406931604-550938be-0d06-47f8-8221-46096dbc80e9.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mb-2 mt-4">查询出所有没有奖金的员工</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE comm IS NULL;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406932103-78fc5e77-3fda-43a4-b067-f9d6a5f7b08e.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">2.6.5 范围查询</h4>
                <p class="mb-2">范围查询可以由IN或者NOT IN 来查询，IN在上面已经讲过。</p>
                <p class="mb-2">查询10部门以外的所有的员工</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE deptno NOT IN (10);</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406932358-525114ed-5eb5-402f-b3e3-4c409910a52a.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">2.6.6 模糊查询</h4>
                <p class="mb-2">模糊查询的由LIKE，NOT LIKE。LIKE是满足条件，NOT LIKE 不满足条件。</p>
                <p class="mb-2">匹配规则：</p>
                <div class="bg-gray-100 p-4 rounded-lg mb-4">
                    <p>%：表示任意0个或多个字符。可匹配任意类型和长度的字符</p>
                    <p>_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：</p>
                </div>
                <p class="mb-2">查询出员工姓名以M开头的所有员工</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE ename LIKE 'M%';</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406932728-8810f4ba-8ec1-46c8-95c5-0637739190b5.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mb-2 mt-4">查询出员工姓名以非M开头的所有员工</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE ename NOT LIKE 'M%';</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406933267-76e893ea-c354-48ae-8fa6-0179ffb5355d.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mb-2 mt-4">查询员工姓名的第2个字母为"M"的员工信息</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE ename LIKE '_M%';</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406933829-cc8dd465-1d9e-46ca-a049-5b602038f126.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">2.6.7 排序</h4>
                <p class="mb-2">在sql语句中可以通过ORDER BY 语句完成数据的排序。还可以通过ASC(升序)和DSC(降序)来指定升序还是降序，默认是按照升序排序。</p>
                <p class="mb-2">查询所有员工信息，并且按照工资由高到底排序。</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp ORDER BY sal DESC;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406934318-297f2b73-bbf3-4139-992f-c2dd341f0d40.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">2.6.8 查询除了ALLEN以外的所有员工信息</h4>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE ename != 'ALLEN';</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406934865-a0919628-e9e3-4dc6-9bed-3407dde83ef0.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 2.7 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">2.7 BETWEEN</h3>
                <p class="mb-4">操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。</p>
                <p class="mb-2">查询工资在1100到2850之间员工信息</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE sal BETWEEN 1100 AND 2850;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406935408-73c08e40-9b76-42b3-bae0-4e848014020f.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
        </section>

        <!-- Multi-Table Queries -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-project-diagram section-icon"></i>
                <span>3. 多表查询</span>
            </h2>
            <p class="mb-6 text-lg">多表查询就是就是在一条sql语句中，从多张表里面一起取出所需要的数据，如果要进行多表查询，直接在FROM语句后跟上多个表即可。多表查询的时候会出现一种现象"笛卡尔积",消除笛卡尔积就需要表和表的一个关联条件了。</p>
            
            <!-- 3.1 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">3.1 产生笛卡尔积</h3>
                <p class="mb-4">下面的查询数据中就出现了笛卡尔积现象，总共出现了56条数据，出现这种现象的原因就是没有加emp表和dept表的关联条件，每张表的每条数据都可以匹配成功。</p>
                <p class="mb-4">emp表中总共有14条数据，dept表中总共有4条数据。所以最后的结果是14*4=56条。</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp, dept;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406935862-7cfcbe4e-9b5c-4115-8fbb-3b6716d88d99.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mt-4">解决笛卡尔积的问题只需要加表和表之间的关联条件，emp表和dept表之间通过deptno关联。所以加了关联条件就可以消除笛卡尔积。</p>
            </div>
            
            <!-- 3.2 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">3.2 消除笛卡尔积</h3>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406936384-791ff018-633f-4efd-9b9a-bfa40bcdc2ea.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mt-4">此时的结果是14条数据，才属于正常。</p>
            </div>
            
            <!-- 3.3 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">3.3 查询所有员工，要求显示员工编号，员工姓名，部门编号，部门名称</h3>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT emp.empno, emp.ename, emp.deptno, dept.dname 
FROM emp, dept 
WHERE emp.deptno = dept.deptno;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406936996-0c8c12ed-3359-4b20-b95b-78087552e784.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 3.4 -->
            <div class="card bg-white rounded-lg shadow-md p-6">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">3.4 链接查询</h3>
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">3.4.1 内连接</h4>
                <p class="mb-2">查询两种表中满足条件的数据，查询所有的员工信息和部门信息，没有部门的员工不显示</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT e.empno, e.ename, e.deptno, d.dname 
FROM emp e 
INNER JOIN dept d ON e.deptno = d.deptno;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406937410-4ce425ca-a24e-4f18-bf9e-20e52aed18be.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">3.4.2 自连接</h4>
                <p class="mb-2">自连接就是两张关联表都是同一张表，通过自身关联来查询。</p>
                <p class="mb-2">1.查询出每个员工的员工编号，员工姓名，领导编号，领导姓名</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT e.empno, e.ename, e.mgr, m.ename AS mgr_name 
FROM emp e 
LEFT JOIN emp m ON e.mgr = m.empno;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406938019-27fedbed-66a1-4b94-a501-5569d31d00dc.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">3.4.3 左链接</h4>
                <p class="mb-2">左链接以左表为主，左边的数据全部显示，右边数据的如果匹配到了就显示，没有匹配到就用空值代替。</p>
                <p class="mb-2">1. 查询所有的员工的的员工编号，员工姓名，部门编号，部门名称</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT e.empno, e.ename, e.deptno, d.dname 
FROM emp e 
LEFT JOIN dept d ON e.deptno = d.deptno;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406938552-6f8402ee-c297-49f1-807d-c6b2d2f3622a.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">3.4.4 右链接</h4>
                <p class="mb-2">右链接以右表为主，右边的数据全部显示，左边数据的如果匹配到了就显示，没有匹配到就用空值代替。</p>
                <p class="mb-2">1. 查询所有的部门下的员工编号，员工姓名，部门编号，部门名称</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT e.empno, e.ename, d.deptno, d.dname 
FROM emp e 
RIGHT JOIN dept d ON e.deptno = d.deptno;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406939015-fb63b822-57f9-48b7-8726-1260adadbfc3.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mt-4">由于是右链接以右边为主，所以40部门下面没有员工就用空置来代替</p>
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">3.4.5 结果集合并</h4>
                <p class="mb-2">将两个两个查询结果集合并可以通过UNION或UNION ALL来操作。</p>
                <p class="mb-2">UNION:返回查询结果的全部内容，重复数据不显示。</p>
                <p class="mb-4">UNION ALL:返回查询结果的全部内容，重复数据也会显示。</p>
                <p class="mb-2">看下UNION和UNION ALL的区别，面试经常问</p>
                <div class="grid md:grid-cols-2 gap-4">
                    <div>
                        <p class="mb-2">UNION</p>
                        <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406939469-be88eace-6619-47f5-859e-901b12c91414.png" alt="UNION" class="w-full rounded-lg shadow">
                    </div>
                    <div>
                        <p class="mb-2">UNION ALL</p>
                        <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406939961-2f9660c6-9066-4f09-86b8-297788bc419b.png" alt="UNION ALL" class="w-full rounded-lg shadow">
                    </div>
                </div>
            </div>
        </section>

        <!-- Common Functions -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-calculator section-icon"></i>
                <span>4. 常用函数</span>
            </h2>
            
            <!-- 4.1 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">4.1 字符函数</h3>
                <div class="table-container">
                    <table class="w-full">
                        <thead>
                            <tr>
                                <th>序号</th>
                                <th>函数名称</th>
                                <th>描述</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>1</td>
                                <td>UPPER(列|字符串)</td>
                                <td>将字符串的内容全部转为大写</td>
                            </tr>
                            <tr>
                                <td>2</td>
                                <td>LOWER(列|字符串)</td>
                                <td>将字符串的内容全部转为小写</td>
                            </tr>
                            <tr>
                                <td>3</td>
                                <td>LENGTH(列|字符串)</td>
                                <td>求出字符串的长度</td>
                            </tr>
                            <tr>
                                <td>4</td>
                                <td>SUBSTR(列|字符串,起始索引,长度)</td>
                                <td>截取字符串</td>
                            </tr>
                            <tr>
                                <td>5</td>
                                <td>REPLACE(列|字符串)</td>
                                <td>字符串替换</td>
                            </tr>
                            <tr>
                                <td>6</td>
                                <td>TRIM(列|字符串)</td>
                                <td>去掉左右空格</td>
                            </tr>
                            <tr>
                                <td>7</td>
                                <td>INSTR(列|字符串,要查找的字符串)</td>
                                <td>查找字符串中某个字符的索引，如果匹配到就返回索引，匹配不到就返回0</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </div>
            
            <!-- 4.2 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">4.2 数值函数</h3>
                <div class="table-container">
                    <table class="w-full">
                        <thead>
                            <tr>
                                <th>序号</th>
                                <th>函数名称</th>
                                <th>描述</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>1</td>
                                <td>ROUND(数字,保留位数)</td>
                                <td>对小数进行四舍五入，可以指定保留的位数，如果不指定则表示将小数点之后的数字全部进行四舍五入如。</td>
                            </tr>
                            <tr>
                                <td>2</td>
                                <td>MOD(数字,数字)</td>
                                <td>取模</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </div>
            
            <!-- 4.3 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">4.3 日期函数</h3>
                <div class="table-container mb-6">
                    <table class="w-full">
                        <thead>
                            <tr>
                                <th>序号</th>
                                <th>函数名称</th>
                                <th>描述</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>1</td>
                                <td>ADDDATE(日期，数字)</td>
                                <td>制定的日期加上指定的天数，求出新的日期</td>
                            </tr>
                            <tr>
                                <td>2</td>
                                <td>LAST_DAY(日期)</td>
                                <td>求出指定日期的最后一天</td>
                            </tr>
                            <tr>
                                <td>3</td>
                                <td>now()</td>
                                <td>获取当前时间</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">4.3.1 获取当前系统时间</h4>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT now();</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406940234-d9932ae3-d48b-4a1c-8889-07991d8861c0.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">4.3.2 ADDDATE</h4>
                <p class="mb-2">制定的日起加上指定的天数，求出新的日期</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT ADDDATE('2020-01-01', 10);</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406940540-7a6140fa-36f8-444c-84c8-d3e6fbf3c166.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">4.3.3 LAST_DAY</h4>
                <p class="mb-2">求出当前月的最后一天</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT LAST_DAY('2020-02-01');</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406941073-071fff00-fc68-42e5-9f7d-abc4a5d51033.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 4.5 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">4.5 通用函数</h3>
                <div class="table-container mb-6">
                    <table class="w-full">
                        <thead>
                            <tr>
                                <th>序号</th>
                                <th>函数名称</th>
                                <th>描述</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>1</td>
                                <td>IFNULL(数字|列,默认值)</td>
                                <td>如果显示数据是null，就使用默认值</td>
                            </tr>
                            <tr>
                                <td>2</td>
                                <td>CASE 列|数值 WHEN 表达式1 THEN 显示结果1 … ELSE 表达式2 END</td>
                                <td>用于实现多条件判断，在WHERE之后编写条件，而在THEN之后编写条件满足的显示操作，如果都不满足则使用ELSE中的表达式处理</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">4.5.1 IFNULL</h4>
                <p class="mb-2">IFNULL函数的作用是如果值为null，就用默认值显示。</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT ename, IFNULL(comm, 0) AS comm FROM emp;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406941643-4242b4e4-e176-48a2-8294-108ac22ce70f.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">4.5.2 CASE WHEN</h4>
                <p class="mb-2">该函数可以代替if ...else </p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT ename, sal, 
CASE WHEN sal > 2000 THEN '高工资' 
     WHEN sal > 1000 THEN '中等工资' 
     ELSE '低工资' 
END AS salary_level 
FROM emp;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406942286-407637ec-e7f0-4e61-85b9-49d9a8bae524.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 4.6 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">4.6 统计函数</h3>
                <div class="table-container mb-6">
                    <table class="w-full">
                        <thead>
                            <tr>
                                <th>序号</th>
                                <th>函数名称</th>
                                <th>描述</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>1</td>
                                <td>COUNT(*|列)</td>
                                <td>求出全部的记录数</td>
                            </tr>
                            <tr>
                                <td>2</td>
                                <td>SUM(列)</td>
                                <td>求出总和</td>
                            </tr>
                            <tr>
                                <td>3</td>
                                <td>AVG(列)</td>
                                <td>平均值</td>
                            </tr>
                            <tr>
                                <td>4</td>
                                <td>MAX(列)</td>
                                <td>最大值</td>
                            </tr>
                            <tr>
                                <td>5</td>
                                <td>MIN(列)</td>
                                <td>最小值</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">4.6.1 COUNT</h4>
                <p class="mb-2">1.查询总共有多少员工</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT COUNT(*) FROM emp;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406942688-eb6ae370-c3bc-4f2a-8575-485c2b4fe023.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">4.6.2 SUM</h4>
                <p class="mb-2">1.求出每个月工资支出的总和</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT SUM(sal) FROM emp;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406943211-67a0869e-a7dd-47f1-b5fd-aa874de27510.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <h4 class="text-xl font-semibold mt-6 mb-4 text-indigo-600">4.6.3 AVG,MAX,MIN</h4>
                <p class="mb-2">1 求出公司的最高工资，最低工资，平均工资。</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT MAX(sal), MIN(sal), ROUND(AVG(sal), 2) FROM emp;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406943678-c4c51374-2fb1-45f8-a79c-d7d3bab3005d.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mt-4">在算平均的工资的时候会出现小数，所以用ROUND函数保留小数点后两位。</p>
            </div>
        </section>

        <!-- Statistical Queries -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-chart-bar section-icon"></i>
                <span>5. 统计查询</span>
            </h2>
            
            <!-- 5.1 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">5.1 单字段分组统计</h3>
                <p class="mb-2">1．查询出每个部门的人数</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406943913-2b81b8e8-e0ae-4bc1-9b35-e2b5d1fd4442.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <p class="mb-2 mt-6">2．查询查每个部门的部门名称，部门人数，平均工资</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT d.dname, COUNT(*), IFNULL(ROUND(AVG(e.sal), 2), 0) 
FROM emp e 
RIGHT JOIN dept d ON e.deptno = d.deptno 
GROUP BY d.dname;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406944247-2d3c03d2-da5b-42f8-9377-30da98cb16a0.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mt-4">由于要统计部门名称所以要和部门表进行关联，既然要统计部门那就要以部门表为主，所以要右链接，在算平均值的可能会出现小数点多为的情况，所以用了ROUND函数保留了两位小数。由于40部门下没有人，也就没有平均工资所以会出现空的情况，用IFNULL函数如果为空就显示0。</p>
            </div>
            
            <!-- 5.2 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">5.2 多字段分组统计</h3>
                <p class="mb-2">1.查询查每个部门的部门编号，部门名称，部门人数，平均工资</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT d.deptno, d.dname, COUNT(*), IFNULL(ROUND(AVG(e.sal), 2), 0) 
FROM emp e 
RIGHT JOIN dept d ON e.deptno = d.deptno 
GROUP BY d.deptno, d.dname;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406944768-d43e506d-b655-4c02-a752-7c52ff604c00.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mt-4">在Oracle中<span class="highlight">分组后select语句后面只能写分组字段或者统计函数</span>，所以这里要按照两个字段来分组才能行。</p>
            </div>
            
            <!-- 5.3 -->
            <div class="card bg-white rounded-lg shadow-md p-6">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">5.3 HAVING</h3>
                <p class="mb-4">在很多时候往往需要对分组之后的数据进行再次过滤，例如：要求出部门人数超过5个人的部门信息，这样的操作首先要按照部门进行分组统计，而后在通过统计结果进行数据过滤，要想实现这样的功能，只能通过HAVING语句来完成。</p>
                
                <p class="mb-2">1.查询出所有平均工资大于2000元的职位信息，平均工资，员工人数</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT job, AVG(sal), COUNT(*) 
FROM emp 
GROUP BY job 
HAVING AVG(sal) > 2000;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406945252-6273fb8c-0e07-41a8-9f6d-bca826c637c9.png" alt="查询结果" class="w-full rounded-lg shadow">
                
                <p class="mb-2 mt-6">2. 求出部门人数大于5人的部门，要求显示部门名称，部门编号，部门人数。</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT d.deptno, d.dname, COUNT(*) 
FROM emp e 
RIGHT JOIN dept d ON e.deptno = d.deptno 
GROUP BY d.deptno, d.dname 
HAVING COUNT(*) > 5;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406945762-9cdf0361-a588-4dbd-8f5f-7cf35f6daefb.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
        </section>

        <!-- Subqueries -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-code-branch section-icon"></i>
                <span>6. 子查询</span>
            </h2>
            <p class="mb-6 text-lg">子查询就是指在一个完整的查询语句中，嵌套若干个不同功能的小查询，从而完成一个复杂的查询。<span class="highlight">把一个查询的结果当成另一个查询的条件</span></p>
            
            <!-- 6.1 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">6.1 子查询返回单行单列数据</h3>
                <p class="mb-2">1.查询基本工资比ALLEN低的员工信息</p>
                <p class="mb-4">想要完成这个查询，首要要查出ALLEN的工资，如下：</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT sal FROM emp WHERE ename = 'ALLEN';</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406946310-ab72abcb-fc3f-463f-b5a7-f6ea22fbd9f5.png" alt="查询结果" class="w-full rounded-lg shadow mb-4">
                <p class="mb-2">现在查询除了ALLEN的工资后把它当成一个条件再去查询就可以完成上面的查询，如下：</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp WHERE sal < (SELECT sal FROM emp WHERE ename = 'ALLEN');</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406946832-4f7aa7c8-5f0d-469c-b79c-b6fc1d5df7fd.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 6.2 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">6.2 子查询返回单行多列数据</h3>
                <p class="mb-2">1. 查询与员工7566从事同一工作且领导相同的全部员工信息</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp 
WHERE (job, mgr) = (SELECT job, mgr FROM emp WHERE empno = 7566);</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406947067-90c5723a-1f0c-4df3-808e-6e2d63989dab.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 6.3 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">6.3 子查询返回多行多列数据</h3>
                <p class="mb-2">1. 查询与员工7566,7369从事同一工作且领导相同的全部员工信息</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp 
WHERE (job, mgr) IN (SELECT job, mgr FROM emp WHERE empno IN (7566, 7369));</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406947365-f633bbea-5bdb-4968-8105-65b08e87e98e.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 6.4 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">6.4 在FROM字句中使用子查询</h3>
                <p class="mb-4">如果子查询返回的数据都是多行多列，则可以将其当做一张临时数据表，并且这种子查询一般都出现在FROM字句中。</p>
                <p class="mb-2">1.查询出每个部门的部门编号，部门名称，部门人数，平均工资</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT d.deptno, d.dname, IFNULL(e.emp_count, 0), IFNULL(e.avg_sal, 0) 
FROM dept d 
LEFT JOIN (
    SELECT deptno, COUNT(*) AS emp_count, ROUND(AVG(sal), 2) AS avg_sal 
    FROM emp 
    GROUP BY deptno
) e ON d.deptno = e.deptno;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406947927-bf6af89b-4c75-4b29-a956-d5ff428cc55f.png" alt="查询结果" class="w-full rounded-lg shadow">
                <p class="mt-4">上面的sql中分了两次查询，一次是查询部门数据，一次是查询员工的统计数据。最后将员工的统计数据当成一个临时表和部门变关联查询，由于要显示部门的数据，所以要以部门表为准，所以要用左链接。</p>
            </div>
            
            <!-- 6.5 -->
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">6.5 在WHERE字句中使用子查询</h3>
                <p class="mb-4">如果返回的数据是单行单列，也可以在WHERE字句中使用子查询</p>
                <p class="mb-2">1. 查询部门名称为<span class="highlight">ACCOUNTING</span>下面的所有的员工</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp 
WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'ACCOUNTING');</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406948390-31814f7d-a4cf-4954-acd6-61af4c17da42.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <!-- 6.6 -->
            <div class="card bg-white rounded-lg shadow-md p-6">
                <h3 class="text-2xl font-semibold mb-4 text-indigo-700">6.6 在SELECT语句中使用子查询</h3>
                <p class="mb-2">1.查询出每个员工的部门名称</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT e.ename, (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) AS dname 
FROM emp e;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406948899-c409a992-5c3f-443b-bbf6-1e3e57368cb2.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
        </section>

        <!-- Pagination -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-file-alt section-icon"></i>
                <span>7. 分页</span>
            </h2>
            <p class="mb-6 text-lg">在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这时候可以利用LIMIT来完成。LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。</p>
            
            <div class="bg-gray-100 p-4 rounded-lg mb-6">
                <p>语法如下：</p>
                <p class="highlight">select 字段 from 表 limit 偏移量,数量</p>
            </div>
            
            <div class="card bg-white rounded-lg shadow-md p-6 mb-8">
                <p class="mb-4">例1：查询emp表前3条数据</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp LIMIT 0, 3;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406949529-1483bdbd-64e3-481b-90c5-b9ce7da24309.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
            
            <div class="card bg-white rounded-lg shadow-md p-6">
                <p class="mb-4">列2:在查询emp表中查询数据从第3行开始查询5条数据</p>
                <div class="code-block p-4 rounded-lg mb-4">
                    <pre class="text-gray-300">SELECT * FROM emp LIMIT 2, 5;</pre>
                </div>
                <img src="https://cdn.nlark.com/yuque/0/2022/png/21449790/1651406950028-45b5ca3d-38e7-487a-9228-b6fcfb4a0345.png" alt="查询结果" class="w-full rounded-lg shadow">
            </div>
        </section>

        <!-- Exercises -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-pen-square section-icon"></i>
                <span>8. 练习题</span>
            </h2>
            <div class="card bg-white rounded-lg shadow-md p-6">
                <p class="text-lg mb-4">准备好实践这些查询技巧了吗？尝试完成以下练习：</p>
                <ol class="list-decimal pl-6 space-y-2">
                    <li>查询工资最高的员工信息</li>
                    <li>查询每个部门的最高工资和最低工资</li>
                    <li>查询领导人数超过3人的部门信息</li>
                    <li>查询平均工资高于公司平均工资的部门</li>
                    <li>使用子查询优化你的查询语句</li>
                </ol>
            </div>
        </section>

        <!-- MySQL Encoding -->
        <section>
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-code section-icon"></i>
                <span>9. 查询MySQL各种编码</span>
            </h2>
            <div class="card bg-white rounded-lg shadow-md p-6">
                <div class="code-block p-4 rounded-lg">
                    <pre class="text-gray-300">show variables like 'character\_set\_%';</pre>
                </div>
            </div>
        </section>
    </div>

    <script>
        mermaid.initialize({
            startOnLoad: true,
            theme: 'default',
            flowchart: {
                useMaxWidth: true,
                htmlLabels: true,
                curve: 'basis'
            }
        });
    </script>
</body>
</html>
```